using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NCT.Framework;
using System.Data;
using System.Data.Common;
using NCT.Framework.Database;

namespace VISS.API.Commons {
	class PropertyCategoryDA {

        private const string SP_GetByID = "PropertyCategory_GetByID";
        private const string SP_GetByStatus = "PropertyCategory_GetByStatus";
        private const string SP_Insert = "PropertyCategory_Insert";
        private const string SP_Update = "PropertyCategory_Update";
        private const string SP_UpdateOrdering = "PropertyCategory_UpdateOrdering";

		public static PropertyCategoryEnt CreateFromReader(IDataReader reader) {
			PropertyCategoryEnt item = new PropertyCategoryEnt();
			item.ID = ConvertUtils.ToShort(reader["ID"]);
			item.Name = ConvertUtils.ToString(reader["Name"]);
			item.Status = (EPropertyCategoryStatus)ConvertUtils.ToByte(reader["Status"]);
			item.Ordering = ConvertUtils.ToShort(reader["Ordering"]);
			item.DateCreated = ConvertUtils.ToDateTime(reader["DateCreated"]);
			return item;
		}

        public static PropertyCategoryEnt GetByID(short iD, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            PropertyCategoryEnt result = null;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_GetByID)) {
                db.AddParameter(cmd, "ID", iD);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = CreateFromReader(reader);
                    }
                }
            }
            return result;
        }

        public static List<PropertyCategoryEnt> GetByStatus(EPropertyCategoryStatus status, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            List<PropertyCategoryEnt> result = new List<PropertyCategoryEnt>();
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_GetByStatus)) {
                db.AddParameter(cmd, "Status", (byte)status);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    while (reader.Read()) {
                        result.Add(CreateFromReader(reader));
                    }
                }
            }
            return result;
        }

        public static int Insert(PropertyCategoryEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_Insert)) {
                db.AddParameterOuter(cmd, "ID", DbType.Int16);
                db.AddParameter(cmd, "Name", item.Name);
                db.AddParameter(cmd, "Status", item.Status);
                db.AddParameter(cmd, "Ordering", item.Ordering);
                db.AddParameter(cmd, "DateCreated", item.DateCreated);

                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                    item.ID = ConvertUtils.ToShort(cmd.Parameters["ID"].Value);
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                    item.ID = ConvertUtils.ToShort(cmd.Parameters["ID"].Value);
                }
            }
            return result;
        }

        public static int Update(PropertyCategoryEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_Update)) {
                db.AddParameter(cmd, "ID", item.ID);
                db.AddParameter(cmd, "Name", item.Name);
                db.AddParameter(cmd, "Status", item.Status);
                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                }
            }
            return result;
        }

        public static int UpdateOrdering(PropertyCategoryEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_UpdateOrdering)) {
                db.AddParameter(cmd, "ID", item.ID);
                db.AddParameter(cmd, "Ordering", item.Ordering);
                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                }
            }
            return result;
        }

	}
}